/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package uit.tkorg.coauthorgraph.dbaccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import uit.tkorg.coauthorgraph.dto.AuthorDTO;

/**
 *
 * @author Huynh Ngoc Tin
 */
public class AuthorMapper extends MapperDB {

    public AuthorMapper() throws Exception {
            super();
    }

    public AuthorMapper(Connection con) {
            super(con);
            // TODO Auto-generated constructor stub
    }

    /**
     * check If the author exist in DB
     * @param authorDTO
     * @return
     * @throws Exception
     */
    public boolean isAuthorExisted(AuthorDTO authorDTO) throws Exception {
        boolean isExist = false;
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT * FROM coauthorgraph.author author, coauthorgraph.authorinstance authorinstance");
            sql.append(" WHERE author.authorID = authorinstance.authorID ");
            sql.append(" AND authorinstance.instanceName = ?");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.setString(1, authorDTO.getAuthorName());
            ResultSet rs = stmt.executeQuery();
            if ((rs != null) && (rs.next())) {
                isExist = true;
            }
            stmt.close();
        }
        catch (Exception e) {
            System.out.println(e.toString());
            throw e;
        }

        return isExist;
    }

    /**
     *
     * @param authorName
     * @return: authorID or -1 if not exist
     * @throws Exception
     */
    public int getAuthorID(String authorName) throws Exception {
        int authorID = -1;
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT author.authorID FROM coauthorgraph.author author, coauthorgraph.authorinstance authorinstance");
            sql.append(" WHERE author.authorID = authorinstance.authorID ");
            sql.append(" AND authorinstance.instanceName = ?");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.setString(1, authorName);
            ResultSet rs = stmt.executeQuery();
            if ((rs != null) && (rs.next())) {
                authorID = rs.getInt("authorID");
            }

            stmt.close();
        }
        catch (Exception e) {
            System.out.println(e.toString());
            throw e;
        }

        return authorID;
    }

     /**
     *
     * @param authorName
     * @return: authorID or -1 if not exist
     * @throws Exception
     */
    public String getAuthorName(int authorID) throws Exception {
        String authorName = null;
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT author.authorName FROM coauthorgraph.author author");
            sql.append(" WHERE author.authorID = ?");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.setInt(1, authorID);
            ResultSet rs = stmt.executeQuery();
            if ((rs != null) && (rs.next())) {
                authorName = rs.getString("authorName");
            }

            stmt.close();
        }
        catch (Exception e) {
            System.out.println(e.toString());
            throw e;
        }

        return authorName;
    }

    /**
     * insert a new author into the DB
     * @param authorDTO
     * @throws Exception
     */
    public int insertNewAuthor(AuthorDTO authorDTO) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("INSERT INTO coauthorgraph.author(authorName, affiliation, emailAddress, webPage, image) ");
            sql.append(" VALUES(?, ?, ?, ?, ?)");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.setString(1, authorDTO.getAuthorName());
            stmt.setString(2, authorDTO.getAffiliation());
            stmt.setString(3, authorDTO.getEmailAddress());
            stmt.setString(4, authorDTO.getWebPage());
            stmt.setString(5, authorDTO.getImage());

            stmt.execute();

            // get the last inserted ID
            ResultSet rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
            int insertAuthorID = -1;
            if (rs != null && rs.next())
                insertAuthorID = rs.getInt(1);
            stmt.close();
            return insertAuthorID;
        }
        catch (Exception e) {
            System.out.println(e.toString());
            throw e;
        }
    }

    /**
     * getAuthorIDListFromnameList
     * @param authorNameList
     * @return
     * @throws Exception
     */
    public ArrayList getAuthorIDListFromNameList(ArrayList authorNameList) throws Exception {
        ArrayList authorIDList = new ArrayList();
        int authorID = -1;
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT author.authorID FROM coauthorgraph.author author, coauthorgraph.authorinstance authorinstance");
            sql.append(" WHERE author.authorID = authorinstance.authorID ");
            sql.append(" AND authorinstance.instanceName IN (");
            for (int i=0; i<authorNameList.size(); i++) {
                if (i != (authorNameList.size()-1))
                    sql.append("?,");
                else
                    sql.append("?");

            }
            sql.append(")");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            for (int i=0; i<authorNameList.size(); i++) {
                stmt.setString(i+1, (String) authorNameList.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            while ((rs != null) && (rs.next())) {
                authorID = rs.getInt("authorID");
                authorIDList.add(new Integer(authorID));
            }

            rs.close();
            stmt.close();
        }
        catch (Exception e) {
            System.out.println(e.toString());
            throw e;
        }
        
        return authorIDList;
    }
}
